﻿'bram debouvere
'
Option Strict On

Imports System.Data.OleDb
Imports System.Collections.Generic
Imports Microsoft.Office.Interop
Imports System.Xml.Linq


Public Class GiftList
    Private _id As Integer
    Private _naam As String
    Private _datumAanmaak As String
    Private _gelegenheidId As Integer
    Private _lijstPerId As Integer
    Private _lijstCode As String
    Private _lijstActief As Boolean
    Private _perNaam As String
    Private _gelegenheidNaam As String

    Public Sub New(ByVal Id As Integer, ByVal Naam As String, ByVal DatumAanmaak As String, ByVal GelegenheidId As Integer, ByVal LijstPerId As Integer, ByVal LijstCode As String, ByVal LijstActief As Boolean, ByVal PerNaam As String, ByVal GelegenheidNaam As String)
        _id = Id
        _naam = Naam
        _datumAanmaak = DatumAanmaak
        _gelegenheidId = GelegenheidId
        _lijstPerId = LijstPerId
        _lijstCode = LijstCode
        _lijstActief = LijstActief
        _perNaam = PerNaam
        _gelegenheidNaam = GelegenheidNaam
    End Sub

    Public Property Id As Integer
        Get
            Return _id
        End Get
        Set(ByVal Value As Integer)
            _id = Value
        End Set
    End Property

    Public Property Naam As String
        Get
            Return _naam
        End Get
        Set(ByVal Value As String)
            _naam = Value
        End Set
    End Property

    Public Property DatumAanmaak As String
        Get
            Return _datumAanmaak
        End Get
        Set(ByVal Value As String)
            _datumAanmaak = Value
        End Set
    End Property

    Public Property GelegenheidId As Integer
        Get
            Return _gelegenheidId
        End Get
        Set(ByVal Value As Integer)
            _gelegenheidId = Value
        End Set
    End Property

    Public Property LijstPerId As Integer
        Get
            Return _lijstPerId
        End Get
        Set(ByVal Value As Integer)
            _lijstPerId = Value
        End Set
    End Property

    Public Property LijstCode As String
        Get
            Return _lijstCode
        End Get
        Set(ByVal Value As String)
            _lijstCode = Value
        End Set
    End Property

    Public Property LijstActief As Boolean
        Get
            Return _lijstActief
        End Get
        Set(ByVal Value As Boolean)
            _lijstActief = Value
        End Set
    End Property

    Public Property PerNaam As String
        Get
            Return _perNaam
        End Get
        Set(ByVal Value As String)
            _perNaam = Value
        End Set
    End Property

    Public Property GelegenheidNaam As String
        Get
            Return _gelegenheidNaam
        End Get
        Set(ByVal Value As String)
            _gelegenheidNaam = Value
        End Set
    End Property

    Public Shared Function GetGiftListsFromDatabase(ByRef dbh As DatabaseHelper) As List(Of GiftList)
        Dim list As New List(Of GiftList)
        'persoonnaam als extra item ophalen
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT GeschenkLijst.* , Persoon.PerNaam + ' ' + Persoon.PerVoornaam AS PerNaam, Gelegenheid.GelegenheidNaam FROM Geschenklijst,Persoon,Gelegenheid WHERE GeschenkLijst.LijstPerId = Persoon.PerId AND LijstGelegenheidId=GelegenheidID ORDER BY Gelegenheid.GelegenheidNaam ASC", {})
        Do While reader.Read()
            Dim gList As New GiftList(CInt(reader("LijstId")), CStr(reader("LijstNaam")), CStr(reader("LijstDatumAanmaak")), CInt(reader("LijstGelegenheidId")), CInt(reader("LijstPerId")), CStr(reader("LijstCode")), CBool(reader("LijstActief")), CStr(reader("PerNaam")), CStr(reader("GelegenheidNaam")))
            list.Add(gList)
        Loop
        reader.Close()
        Return list
    End Function


    Function CodeEqualsOtherGiftList(ByRef Code As String, ByRef GiftList As GiftList, ByRef dbh As DatabaseHelper) As Boolean
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT * FROM GeschenkLijst WHERE LijstCode = ? AND NOT LijstId = ?", {Code, GiftList.Id})
        Dim returnvalue As Boolean = True
        If Not reader.Read() Then
            returnvalue = False
        End If
        reader.Close()
        Return returnvalue
    End Function

    Function nameEqualsOtherGiftList(ByRef Name As String, ByRef GiftList As GiftList, ByRef dbh As DatabaseHelper) As Boolean
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT * FROM GeschenkLijst WHERE LijstNaam = ? AND NOT LijstId = ?", {Name, GiftList.Id})
        Dim returnvalue As Boolean = True
        If Not reader.Read() Then
            returnvalue = False
        End If
        reader.Close()
        Return returnvalue
    End Function

    'Tostring method needed for the Order Wizard
    Public Overrides Function toString() As String
        Return _naam
    End Function

    Public Shared Sub AddGiftListToDb(ByRef GiftList As GiftList, ByRef GLPList As List(Of GiftListProduct), ByRef dbh As DatabaseHelper)

        Dim transaction As OleDbTransaction = dbh.DBConnection.BeginTransaction()
        Try
            dbh.ExecuteSQL("INSERT INTO GeschenkLijst (LijstNaam,LijstDatumAanmaak,LijstGelegenheidId,LijstPerId,LijstCode,LijstActief) VALUES (?,?,?,?,?,?)", {GiftList.Naam, GiftList.DatumAanmaak, GiftList.GelegenheidId, GiftList.LijstPerId, GiftList.LijstCode, GiftList.LijstActief}, transaction)
            'get last written giftlist
            Dim reader As OleDbDataReader = dbh.GetReader("SELECT LijstId FROM GeschenkLijst WHERE LijstCode like ?", {GiftList.LijstCode}, transaction)
            reader.Read()
            Dim giftListId As Integer = CInt(reader("LijstId"))
            reader.Close()
            'use the id of that list to fill all the GiftList-Product combinations
            For Each GLP As GiftListProduct In GLPList
                dbh.ExecuteSQL("INSERT INTO GeschenkLijstProduct (GLPProdId,GLPLijstId,GLPCategorie,GLPPrijs,GLPAantal) VALUES (?,?,?,?,?)", {GLP.Product.Id, giftListId, GLP.Category, GLP.Price, GLP.Amount}, transaction)
            Next
            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Sub

    Public Shared Sub UpdateGiftList(ByRef GiftList As GiftList, ByRef NewGiftListProductItems As List(Of GiftListProduct), ByRef Dbh As DatabaseHelper)
        Dim transaction As OleDbTransaction = Dbh.DBConnection.BeginTransaction()
        Try
            'update giftlist
            Dbh.ExecuteSQL("UPDATE GeschenkLijst SET LijstNaam = ?,LijstDatumAanmaak = ?,LijstGelegenheidId = ?,LijstPerId = ?,LijstCode = ?,LijstActief = ?  WHERE LijstId=?", {GiftList.Naam, GiftList.DatumAanmaak, GiftList.GelegenheidId, GiftList.LijstPerId, GiftList.LijstCode, GiftList.LijstActief, GiftList.Id}, transaction)
            'delete all geschenklijstproduct combinations
            'Dbh.ExecuteSQL("DELETE FROM GeschenklijstProduct WHERE GLPLijstId = ?", {GiftList.Id}, transaction)
            'add the updated geschenklijstproduct combinations
            For Each glp As GiftListProduct In NewGiftListProductItems
                'check if item already exists
                Dim reader As OleDbDataReader = Dbh.GetReader("SELECT GLPProdId FROM GeschenklijstProduct WHERE GLPProdId = ? AND GLPLijstId = ?", {glp.Product.Id, glp.Giftlist.Id}, transaction)
                If reader.Read() Then
                    'update if exists
                    Dbh.ExecuteSQL("UPDATE GeschenklijstProduct SET GLPCategorie=?,GLPPrijs=?,GLPAantal=? WHERE GLPProdId=? AND GLPLijstId=?", {glp.Category, glp.Price, glp.Amount, glp.Product.Id, glp.Giftlist.Id}, transaction)
                Else
                    'insert if not exists
                    Dbh.ExecuteSQL("INSERT INTO GeschenklijstProduct (GLPProdId,GLPLijstId,GLPCategorie,GLPPrijs,GLPAantal) VALUES (?,?,?,?,?)", {glp.Product.Id, GiftList.Id, glp.Category, glp.Price, glp.Amount}, transaction)
                End If
                reader.Close()
            Next
            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Sub

    Public Shared Sub removeGiftListFromDb(ByVal giftList As GiftList, ByVal giftlistItems As List(Of GiftListProduct), ByVal dbh As DatabaseHelper)
        Dim transaction As OleDbTransaction = dbh.DBConnection.BeginTransaction()
        Try
            For Each glp As GiftListProduct In giftlistItems
                dbh.ExecuteSQL("DELETE FROM GeschenklijstProduct WHERE GLPProdId = ? AND GLPLijstId = ?", {glp.Product.Id, giftList.Id}, transaction)
            Next
            dbh.ExecuteSQL("DELETE FROM GeschenkLijst WHERE LijstId = ?", {giftList.Id}, transaction)
            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Sub

    Public Shared Function turnDateAround(ByVal aDate As String) As String
        Return aDate.Split(CChar("/"))(2) & "/" & aDate.Split(CChar("/"))(1) & "/" & aDate.Split(CChar("/"))(0)
    End Function

    Public Shared Sub ExportToWord(ByRef file As String, ByRef giftList As GiftList, ByRef giftListProductItems As List(Of GiftListProduct), ByRef startUpPath As String, ByRef dbh As DatabaseHelper)
        'edit filename
        If System.IO.Path.GetExtension(file) <> ".doc" Then
            file += ".doc"
        End If

        'word open
        Dim wordApp As New Word.Application()
        wordApp.Visible = False
        wordApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone 'no alerts

        'new document by template
        Dim doc As Word.Document = wordApp.Documents.Open(startUpPath & "\templates\giftlist.dotx")

        'get settings
        Dim settingsPath As String = startUpPath & "\settings.xml"
        Dim settings = XDocument.Load(settingsPath)
        Dim logo As String = settings.<settings>.<logo>.Value
        Dim imagesFolder As String = settings.<settings>.<imagesFolder>.Value
        Dim firmName As String = settings.<settings>.<firmName>.Value
        Dim btwNummer As String = settings.<settings>.<btwNummer>.Value
        Dim telefoon As String = settings.<settings>.<telefoon>.Value
        Dim plaats As String = settings.<settings>.<plaats>.Value

        'logo
        Dim rngimage As Word.Range = doc.Bookmarks("Logo").Range
        rngimage.InlineShapes.AddPicture(imagesFolder & "\" & logo)

        Order.WriteBookmark("lijstnaam", giftList.Naam, doc)

        'company
        Order.WriteBookmark("bedrijf", firmName, doc)
        Order.WriteBookmark("adres", plaats, doc)
        Order.WriteBookmark("telefoon", telefoon, doc)
        Order.WriteBookmark("btw", btwNummer, doc)

        'customer
        Dim person As Person = person.GetPersonFromDatabase(dbh, giftList.LijstPerId)
        Dim facturatie As Facturation = Facturation.getFacturationForPerson(person, dbh)

        Order.WriteBookmark("klantadres", facturatie.AdresLijn1 & " " & facturatie.AdresLijn2, doc)
        Order.WriteBookmark("klantnaam", person.Naam & " " & person.Voornaam, doc)
        Order.WriteBookmark("klantgemeente", facturatie.Plaats & " " & facturatie.Land, doc)
        Order.WriteBookmark("klantpostcode", facturatie.PostCode, doc)

        'new tabel (giftlist items)
        Dim tabel As Word.Table = doc.Tables.Add(doc.Bookmarks("gegevens").Range, 1, 5)
        'make the headers
        Dim row As Integer = 2
        tabel.Cell(1, 1).Range.Text = "Product"
        tabel.Cell(1, 2).Range.Text = "Categorie"
        tabel.Cell(1, 3).Range.Text = "Max Aantal"
        tabel.Cell(1, 4).Range.Text = "Prijs per eenheid"
        tabel.Cell(1, 5).Range.Text = "Subtotaal"
        Dim price As Decimal = 0
        For Each item As GiftListProduct In giftListProductItems
            tabel.Rows.Add().Select()
            tabel.Cell(row, 1).Range.Text = item.Product.Naam
            tabel.Cell(row, 2).Range.Text = item.Category
            tabel.Cell(row, 3).Range.Text = CStr(item.Amount)
            tabel.Cell(row, 4).Range.Text = CStr((item.Price / item.Amount))
            tabel.Cell(row, 5).Range.Text = CStr(item.Price)
            price += item.Price
            row += 1
        Next

        Order.WriteBookmark("totaleprijs", CStr(price), doc)

        'word close
        Try
            doc.SaveAs(CType(file, Object))
        Catch ex As Exception

        End Try
        wordApp.Quit()
        'wordApp = Nothing
    End Sub

    Public Function importFromExcel(ByRef File As String, ByRef GiftList As GiftList, ByRef StartUpPath As String, ByRef Dbh As DatabaseHelper) As List(Of GiftListProduct)
        'Excel openen
        Dim app As New Excel.Application
        'don't show excel
        app.Visible = False
        'Don't display allerts or errors
        app.DisplayAlerts = False

        'Document open
        Dim book As Excel.Workbook = app.Workbooks.Open(file)

        'The right worksheet
        Dim sheet As Excel.Worksheet = CType(book.Worksheets("Producten"), Excel.Worksheet)

        'Select the correct range
        Dim range As Excel.Range = sheet.Range("A3")

        'List to put the items in
        Dim list As New List(Of GiftListProduct)

        'read the document
        Do Until range.Value Is Nothing
            Dim prod As Product = Product.GetProductById(CInt(range.Value), Dbh)
            Dim amount As Integer = CInt(range.Offset(0, 3).Value)
            Dim item As New GiftListProduct(prod, GiftList, CStr(range.Offset(0, 4).Value), prod.Prijs * amount, amount)
            'add to the list
            list.Add(item)

            'move one row
            range = range.Offset(1, 0)
        Loop

        'Close excel
        app.Quit()

        Return list

    End Function

End Class
